admin / Strike

-- =====================================================================
-- StrikeXi v3 — Seed data (MULTI-FRAMEWORK)
-- Framework 1: FULL NCSC CAF taxonomy (4 Objectives, 14 Principles).
-- Framework 2: UK Cyber Security & Resilience Bill (CSRB) — 4 Objectives,
--              8 Principles, weighted questions, remediation guidance.
-- Each framework is self-contained and scoped by framework_id, so future
-- frameworks are added purely by inserting rows below (no code changes).
-- (Admin user is created by the backend on startup using bcrypt.)
-- =====================================================================

-- ---------- Frameworks ----------
INSERT INTO frameworks (id, name, description, version, sort_order, is_active) VALUES
 ('caf',  'NCSC Cyber Assessment Framework (CAF)',
   'Maturity assessment against the NCSC Cyber Assessment Framework — 4 objectives and 14 principles covering managing security risk, protecting against cyber attack, detecting events and minimising impact.',
   'CAF v3.2', 1, TRUE),
 ('csrb', 'UK Cyber Security & Resilience Bill',
   'Maturity assessment against the UK Cyber Security and Resilience Bill — scope/registration, governance, risk management, supply-chain duties, mandatory 24h/72h incident reporting, operational resilience and regulatory cooperation.',
   '2025', 2, TRUE)
ON CONFLICT (id) DO NOTHING;

-- =====================================================================
-- FRAMEWORK: NCSC CAF
-- =====================================================================

-- ---------- Objectives ----------
INSERT INTO caf_objectives (id, framework_id, title, description, sort_order) VALUES
 ('A', 'caf', 'Managing security risk',             'Appropriate organisational structures, policies, processes and procedures to understand, assess and systematically manage security risks to essential functions.', 1),
 ('B', 'caf', 'Protecting against cyber attack',    'Proportionate security measures in place to protect essential functions and systems from cyber attack.', 2),
 ('C', 'caf', 'Detecting cyber security events',    'Capabilities to ensure security defences remain effective and to detect cyber security events affecting essential functions.', 3),
 ('D', 'caf', 'Minimising the impact of incidents', 'Capabilities to minimise the adverse impact of a cyber security incident on essential functions, including restoration of those functions.', 4)
ON CONFLICT (id) DO NOTHING;

-- ---------- Principles (all 14) ----------
INSERT INTO caf_principles (id, framework_id, objective_id, title, description, sort_order) VALUES
 -- Objective A
 ('A1', 'caf', 'A', 'Governance',                              'Appropriate organisational structures, policies, processes and procedures to understand, assess and manage security risk.', 1),
 ('A2', 'caf', 'A', 'Risk management',                         'Identification, assessment and understanding of security risks; establishment of an overall organisational approach to risk management.', 2),
 ('A3', 'caf', 'A', 'Asset management',                        'Everything required to deliver, maintain or support essential functions is determined and understood.', 3),
 ('A4', 'caf', 'A', 'Supply chain',                            'Understanding and management of security risks to essential functions arising from dependencies on external suppliers.', 4),
 -- Objective B
 ('B1', 'caf', 'B', 'Service protection policies and processes','Defined, implemented, communicated and enforced policies and processes that direct overall security.', 5),
 ('B2', 'caf', 'B', 'Identity and access control',             'Understanding, documenting and controlling access to networks and information systems supporting essential functions.', 6),
 ('B3', 'caf', 'B', 'Data security',                           'Protection of stored or electronically transmitted data from actions that may cause adverse impact.', 7),
 ('B4', 'caf', 'B', 'System security',                         'Protection of network and information systems and technology from cyber attack.', 8),
 ('B5', 'caf', 'B', 'Resilient networks and systems',          'Building resilience against cyber attack and system failure into the design, implementation and operation.', 9),
 ('B6', 'caf', 'B', 'Staff awareness and training',            'Appropriately supporting staff to ensure they make a positive contribution to cyber security.', 10),
 -- Objective C
 ('C1', 'caf', 'C', 'Security monitoring',                     'Monitoring to detect potential security problems and to track the ongoing effectiveness of protective measures.', 11),
 ('C2', 'caf', 'C', 'Proactive security event discovery',      'Detecting anomalous events relevant to potential security compromise that other measures would not detect.', 12),
 -- Objective D
 ('D1', 'caf', 'D', 'Response and recovery planning',          'Well-defined and tested incident management processes in place to ensure continuity of essential functions.', 13),
 ('D2', 'caf', 'D', 'Lessons learned',                         'When an incident occurs, taking steps to understand its root causes and to ensure appropriate remediation.', 14)
ON CONFLICT (id) DO NOTHING;

-- ---------- One representative weighted question per principle ----------
-- 'guidance' supplies the CAF context: why the question is asked, what the
-- principle expects, and what 'good' (Achieved) looks like.
INSERT INTO questions (principle_id, code, text, guidance, weight, sort_order) VALUES
 ('A1', 'A1-Q1',
  'Is there a board-level individual accountable for cyber security, with clear governance structures and policies that are regularly reviewed?',
  'CAF Objective A is about managing security risk. Principle A1 (Governance) expects senior-level direction and oversight of cyber security, with appropriate policies, defined responsibilities and a risk appetite agreed by leadership. ''Achieved'' means a named board-level owner is accountable, governance structures are documented and decisions are reviewed regularly; ''Partially'' means some governance exists but is informal or not consistently reviewed.',
  2.0, 1),
 ('A2', 'A2-Q1',
  'Do you maintain a documented cyber security risk assessment process and a risk register that is reviewed at planned intervals?',
  'Principle A2 (Risk management) requires you to identify, analyse and understand security risks to your essential functions using a repeatable, documented process. ''Achieved'' means risks are systematically assessed, recorded in a maintained register with owners, and reviewed at planned intervals; ''Not achieved'' means risk is managed ad hoc or undocumented.',
  1.5, 1),
 ('A3', 'A3-Q1',
  'Do you maintain a complete and current inventory of the assets (systems, data, services) required to deliver your essential functions?',
  'Principle A3 (Asset management) expects you to know everything needed to deliver, maintain or support your essential functions. You cannot protect what you do not know you have. ''Achieved'' means an authoritative, current inventory of hardware, software, data and dependencies exists with ownership and classification.',
  1.5, 1),
 ('A4', 'A4-Q1',
  'Do you identify, assess and manage the cyber security risks introduced by your suppliers and third parties?',
  'Principle A4 (Supply chain) recognises that dependencies on external suppliers can introduce risk to essential functions. ''Achieved'' means critical suppliers are identified, security expectations are set contractually, and third-party risk is assessed and monitored over time, not just at onboarding.',
  1.5, 1),
 ('B1', 'B1-Q1',
  'Are security policies and processes defined, approved, communicated and enforced across the organisation?',
  'CAF Objective B is about protecting against cyber attack. Principle B1 (Service protection policies and processes) expects security to be directed by clear, approved policies and operational processes that staff actually follow. ''Achieved'' means policies are documented, communicated, enforced and kept current, with exceptions managed formally.',
  1.5, 1),
 ('B2', 'B2-Q1',
  'Is multi-factor authentication enforced and are access rights granted on a least-privilege basis and reviewed regularly?',
  'Principle B2 (Identity and access control) requires you to understand, document and control who and what can access systems supporting essential functions. ''Achieved'' means strong (ideally phishing-resistant) MFA is enforced, access follows least privilege with joiner/mover/leaver processes, and rights are recertified periodically.',
  2.0, 1),
 ('B3', 'B3-Q1',
  'Is sensitive data protected at rest and in transit using appropriate encryption and access controls?',
  'Principle B3 (Data security) expects data that would cause harm if compromised to be protected wherever it is stored or transmitted. ''Achieved'' means sensitive data is classified, encrypted at rest and in transit with managed keys, access is need-to-know, and secure disposal is in place.',
  2.0, 1),
 ('B4', 'B4-Q1',
  'Are systems securely configured, hardened and patched against known vulnerabilities within defined timeframes?',
  'Principle B4 (System security) is about protecting technology from cyber attack through secure design, configuration and maintenance. ''Achieved'' means systems use hardened baselines, unnecessary services are removed, vulnerabilities are scanned for, and critical patches are applied within defined SLAs.',
  2.0, 1),
 ('B5', 'B5-Q1',
  'Are networks and systems designed for resilience, with tested backups and redundancy for essential functions?',
  'Principle B5 (Resilient networks and systems) expects resilience to attack and failure to be built into design and operation. ''Achieved'' means networks are segregated, redundancy exists for essential functions, and backups are taken, protected (e.g. offline/immutable) and their restoration is tested.',
  1.5, 1),
 ('B6', 'B6-Q1',
  'Do staff receive role-appropriate cyber security awareness training on a recurring basis?',
  'Principle B6 (Staff awareness and training) recognises that people are central to security. ''Achieved'' means staff receive recurring, role-appropriate awareness training (including phishing resilience), completion is tracked, and security-positive behaviour is supported.',
  1.0, 1),
 ('C1', 'C1-Q1',
  'Do you collect and centrally monitor security logs from key systems to detect potential security events?',
  'CAF Objective C is about detecting cyber security events. Principle C1 (Security monitoring) expects monitoring capable of detecting potential security problems and confirming controls are effective. ''Achieved'' means logs from key systems are centrally collected, retained, and reviewed against defined detection use cases with timely alerting.',
  1.5, 1),
 ('C2', 'C2-Q1',
  'Do you proactively hunt for and investigate anomalous activity that routine monitoring might miss?',
  'Principle C2 (Proactive security event discovery) goes beyond rule-based monitoring to find threats that would otherwise evade detection. ''Achieved'' means you proactively hunt for anomalies, use threat intelligence, and investigate suspicious activity that standard alerts would not surface.',
  1.5, 1),
 ('D1', 'D1-Q1',
  'Do you have a documented and tested incident response and recovery plan covering your essential functions?',
  'CAF Objective D is about minimising the impact of incidents. Principle D1 (Response and recovery planning) expects well-defined, exercised plans to maintain and restore essential functions. ''Achieved'' means response and recovery plans exist, define roles and escalation, and are tested (e.g. tabletop exercises) at least annually.',
  2.0, 1),
 ('D2', 'D2-Q1',
  'After an incident, do you conduct root-cause analysis and feed lessons learned back into your controls?',
  'Principle D2 (Lessons learned) expects the organisation to learn from incidents and near-misses. ''Achieved'' means structured post-incident reviews and root-cause analysis are performed, and the resulting improvements are fed back into controls and risk treatment.',
  1.0, 1)
ON CONFLICT (code) DO NOTHING;

-- ---------- Weighted answer options (standard CAF 3-point scale) ----------
-- Achieved = 1.0, Partially achieved = 0.5, Not achieved = 0.0
-- Scoped to CAF questions so other frameworks can use their own scale.
INSERT INTO answer_options (question_id, label, score, sort_order)
SELECT q.id, v.label, v.score, v.so
FROM questions q
JOIN caf_principles p ON p.id = q.principle_id AND p.framework_id = 'caf'
CROSS JOIN (VALUES
    ('Achieved',            1.000, 1),
    ('Partially achieved',  0.500, 2),
    ('Not achieved',        0.000, 3)
) AS v(label, score, so);

-- ---------- Remediation suggestion library (principle-specific) ----------
-- Detail text gives concrete mitigation steps and references the principle.
INSERT INTO remediation_suggestions (id, title, detail, priority, effort) VALUES
 ('a1000000-0000-0000-0000-000000000001',
  'Establish cyber governance and board accountability (Principle A1: Governance)',
  'Appoint a named board-level owner (e.g. SIRO) accountable for cyber risk. Approve a cyber security policy set, define reporting lines and risk appetite, and review governance at least annually. This directly addresses CAF Principle A1 (Governance).', 1, 'medium'),
 ('a2000000-0000-0000-0000-000000000002',
  'Implement a structured risk management process (Principle A2: Risk management)',
  'Adopt a documented risk assessment methodology, create and maintain a risk register with named owners and scoring, and review it quarterly. Ensure risks to essential functions are explicitly tracked. Addresses CAF Principle A2 (Risk management).', 1, 'medium'),
 ('a3000000-0000-0000-0000-000000000003',
  'Build and maintain an asset inventory (Principle A3: Asset management)',
  'Establish an authoritative inventory of hardware, software, data and services underpinning essential functions, with ownership and classification, and keep it current through automated discovery where possible. Addresses CAF Principle A3 (Asset management).', 2, 'medium'),
 ('a4000000-0000-0000-0000-000000000004',
  'Manage supply chain security risk (Principle A4: Supply chain)',
  'Identify critical suppliers, embed security requirements into contracts, assess supplier cyber posture, and monitor third-party risk on an ongoing basis. Addresses CAF Principle A4 (Supply chain).', 2, 'high'),
 ('b1000000-0000-0000-0000-000000000005',
  'Define and enforce security policies and processes (Principle B1)',
  'Document, approve and communicate security policies and operational processes; ensure they are enforced and reviewed. Track exceptions formally. Addresses CAF Principle B1 (Service protection policies and processes).', 2, 'medium'),
 ('b2000000-0000-0000-0000-000000000006',
  'Strengthen identity and access control (Principle B2)',
  'Enforce phishing-resistant MFA for all privileged and remote access, apply least-privilege RBAC, implement joiner/mover/leaver processes and conduct periodic access recertification. Maintain break-glass procedures. Addresses CAF Principle B2 (Identity and access control).', 1, 'medium'),
 ('b3000000-0000-0000-0000-000000000007',
  'Protect data at rest and in transit (Principle B3: Data security)',
  'Classify sensitive data, encrypt it at rest and in transit with managed keys, restrict access on a need-to-know basis, and apply secure disposal. Addresses CAF Principle B3 (Data security).', 1, 'medium'),
 ('b4000000-0000-0000-0000-000000000008',
  'Harden and patch systems (Principle B4: System security)',
  'Apply secure baseline configurations, remove unnecessary services, run vulnerability scanning, and patch critical vulnerabilities within defined SLAs (e.g. 14 days). Addresses CAF Principle B4 (System security).', 1, 'high'),
 ('b5000000-0000-0000-0000-000000000009',
  'Engineer network and system resilience (Principle B5)',
  'Design for redundancy, segregate networks, take regular backups stored offline/immutably, and test restoration so essential functions can survive disruption. Addresses CAF Principle B5 (Resilient networks and systems).', 2, 'high'),
 ('b6000000-0000-0000-0000-00000000000a',
  'Deliver staff security awareness and training (Principle B6)',
  'Run recurring, role-based security awareness training and phishing simulations, and track completion. Addresses CAF Principle B6 (Staff awareness and training).', 3, 'low'),
 ('c1000000-0000-0000-0000-00000000000b',
  'Implement centralised security monitoring (Principle C1)',
  'Aggregate logs from key systems into a SIEM/log platform, define use cases and alerting, set retention, and review alerts within defined timeframes. Addresses CAF Principle C1 (Security monitoring).', 2, 'high'),
 ('c2000000-0000-0000-0000-00000000000c',
  'Establish proactive threat discovery (Principle C2)',
  'Introduce threat hunting, anomaly detection and threat intelligence to surface activity routine monitoring would miss. Addresses CAF Principle C2 (Proactive security event discovery).', 3, 'high'),
 ('d1000000-0000-0000-0000-00000000000d',
  'Create and test incident response and recovery plans (Principle D1)',
  'Document incident response and recovery plans covering essential functions, define roles and escalation, and exercise them at least annually (e.g. tabletop tests). Addresses CAF Principle D1 (Response and recovery planning).', 1, 'medium'),
 ('d2000000-0000-0000-0000-00000000000e',
  'Embed a lessons-learned process (Principle D2)',
  'After incidents, perform structured root-cause analysis and post-incident reviews, and feed improvements back into controls and risk treatment. Addresses CAF Principle D2 (Lessons learned).', 3, 'low')
ON CONFLICT (id) DO NOTHING;

-- ---------- Map each principle to its remediation ----------
-- threshold: if the principle's normalised score (0-1) is BELOW this, queue it.
INSERT INTO remediation_mappings (principle_id, remediation_id, threshold) VALUES
 ('A1', 'a1000000-0000-0000-0000-000000000001', 0.700),
 ('A2', 'a2000000-0000-0000-0000-000000000002', 0.700),
 ('A3', 'a3000000-0000-0000-0000-000000000003', 0.700),
 ('A4', 'a4000000-0000-0000-0000-000000000004', 0.700),
 ('B1', 'b1000000-0000-0000-0000-000000000005', 0.700),
 ('B2', 'b2000000-0000-0000-0000-000000000006', 0.700),
 ('B3', 'b3000000-0000-0000-0000-000000000007', 0.700),
 ('B4', 'b4000000-0000-0000-0000-000000000008', 0.700),
 ('B5', 'b5000000-0000-0000-0000-000000000009', 0.700),
 ('B6', 'b6000000-0000-0000-0000-00000000000a', 0.700),
 ('C1', 'c1000000-0000-0000-0000-00000000000b', 0.700),
 ('C2', 'c2000000-0000-0000-0000-00000000000c', 0.700),
 ('D1', 'd1000000-0000-0000-0000-00000000000d', 0.700),
 ('D2', 'd2000000-0000-0000-0000-00000000000e', 0.700)
ON CONFLICT (principle_id, remediation_id) DO NOTHING;


-- =====================================================================
-- FRAMEWORK: UK CYBER SECURITY & RESILIENCE BILL (CSRB)
-- 4 objectives, 8 principles, weighted questions on a 5-point maturity
-- scale, with principle-specific remediation guidance.
-- =====================================================================

-- ---------- CSRB Objectives ----------
INSERT INTO caf_objectives (id, framework_id, title, description, sort_order) VALUES
 ('CSRB-A', 'csrb', 'Scope, Governance & Accountability',
   'Determining whether the organisation is in scope of the Bill, registering with the appropriate regulator, and establishing board-level accountability and governance for cyber resilience duties.', 1),
 ('CSRB-B', 'csrb', 'Cyber Risk Management',
   'Assessing cyber risk to network and information systems and implementing appropriate and proportionate technical and organisational measures, including across the supply chain.', 2),
 ('CSRB-C', 'csrb', 'Incident Reporting & Response',
   'Detecting significant incidents and meeting the Bill''s mandatory reporting duties (24-hour notification, 72-hour detailed report), supported by a tested incident response capability.', 3),
 ('CSRB-D', 'csrb', 'Resilience & Regulatory Cooperation',
   'Maintaining operational resilience and continuity of essential and digital services, cooperating with regulators, and participating in threat information sharing.', 4)
ON CONFLICT (id) DO NOTHING;

-- ---------- CSRB Principles ----------
INSERT INTO caf_principles (id, framework_id, objective_id, title, description, sort_order) VALUES
 ('CSRB-A1', 'csrb', 'CSRB-A', 'Regulatory Scope & Registration',
   'Formally determining whether the organisation falls in scope (operator of essential services, relevant digital service provider, managed service provider, data centre, large load controller or designated critical supplier) and registering with the relevant regulator.', 1),
 ('CSRB-A2', 'csrb', 'CSRB-A', 'Governance, Roles & Accountability',
   'Board-level accountability for cyber resilience obligations, with documented roles, reporting lines and approved policies reviewed at planned intervals.', 2),
 ('CSRB-B1', 'csrb', 'CSRB-B', 'Risk Assessment & Proportionate Measures',
   'Documented cyber risk assessment of network and information systems and implementation of appropriate and proportionate technical and organisational security measures.', 3),
 ('CSRB-B2', 'csrb', 'CSRB-B', 'Supply Chain & Third-Party Risk',
   'Identifying critical suppliers and managed service providers, setting contractual security requirements, and managing third-party cyber risk on an ongoing basis.', 4),
 ('CSRB-C1', 'csrb', 'CSRB-C', 'Detection & Mandatory Reporting (24h/72h)',
   'Detecting significant incidents promptly and meeting the Bill''s mandatory reporting timelines: regulator/NCSC notification within 24 hours and a detailed report within 72 hours.', 5),
 ('CSRB-C2', 'csrb', 'CSRB-C', 'Incident Response & Recovery',
   'A documented, tested incident response plan with defined roles, escalation and regulator-communication steps, exercised regularly.', 6),
 ('CSRB-D1', 'csrb', 'CSRB-D', 'Operational Resilience & Continuity',
   'Business continuity and disaster recovery capability — including protected, tested backups — so essential and digital services can be restored within agreed timeframes.', 7),
 ('CSRB-D2', 'csrb', 'CSRB-D', 'Regulatory Cooperation & Information Sharing',
   'Cooperating with regulators (information requests, inspections, enforcement directions) and participating in threat information sharing such as NCSC advisories.', 8)
ON CONFLICT (id) DO NOTHING;

-- ---------- CSRB Questions ----------
INSERT INTO questions (principle_id, code, text, guidance, weight, sort_order) VALUES
 ('CSRB-A1', 'CSRB-A1-Q1',
  'Have you formally determined whether your organisation is in scope of the Cyber Security and Resilience Bill (e.g. operator of essential services, relevant digital service provider, managed service provider, data centre, large load controller or designated critical supplier)?',
  'The Bill expands the NIS Regulations to bring more entities into scope, including managed service providers, data centres and organisations designated as critical suppliers. ''Embedded'' means you have formally assessed and documented your scope status against the Bill''s criteria and keep it under review; ''Not in place'' means scope has not been considered.',
  2.0, 1),
 ('CSRB-A1', 'CSRB-A1-Q2',
  'Where in scope, have you registered or notified the appropriate regulator and do you keep your registration details accurate and current?',
  'In-scope entities are required to identify themselves to, and register with, the relevant regulator. ''Embedded'' means registration is complete, ownership is assigned and details are kept current; lower levels reflect partial, out-of-date or absent registration.',
  1.5, 2),
 ('CSRB-A2', 'CSRB-A2-Q1',
  'Is there a board-level individual formally accountable for compliance with the Bill''s cyber resilience obligations, with documented roles and reporting lines?',
  'The Bill places accountability for cyber resilience at senior level. ''Embedded'' means a named board-level owner is accountable, responsibilities are documented and cyber resilience is reported to the board on a regular cycle.',
  2.0, 1),
 ('CSRB-A2', 'CSRB-A2-Q2',
  'Are your duties under the Bill reflected in approved cyber security and resilience policies that are reviewed at planned intervals?',
  'Governance must translate into policy. ''Embedded'' means policies covering the Bill''s duties are approved, communicated, enforced and reviewed at planned intervals; lower levels reflect informal or unreviewed policies.',
  1.5, 2),
 ('CSRB-B1', 'CSRB-B1-Q1',
  'Do you carry out documented cyber risk assessments of your network and information systems and implement appropriate and proportionate measures to manage those risks?',
  'The Bill requires ''appropriate and proportionate'' technical and organisational measures based on the risk presented. ''Embedded'' means risk is assessed using a repeatable documented method, recorded with owners, and measures are demonstrably proportionate to the risk.',
  2.0, 1),
 ('CSRB-B1', 'CSRB-B1-Q2',
  'Are baseline technical controls (access control, MFA, patching, secure configuration and monitoring) implemented and maintained commensurate with the risk?',
  'Proportionate measures include core technical controls. ''Embedded'' means MFA, least-privilege access, timely patching, hardened configuration and monitoring are implemented, maintained and evidenced; lower levels reflect partial or inconsistent control coverage.',
  1.5, 2),
 ('CSRB-B2', 'CSRB-B2-Q1',
  'Have you identified your critical suppliers and managed service providers and assessed the cyber risk they introduce to your services?',
  'The Bill introduces explicit supply-chain duties and brings managed service providers into scope. ''Embedded'' means critical suppliers/MSPs are identified, their risk is assessed and tiered, and dependencies are understood.',
  2.0, 1),
 ('CSRB-B2', 'CSRB-B2-Q2',
  'Do supplier contracts include cyber security requirements, assurance/audit rights and incident-notification obligations?',
  'Managing supply-chain risk includes contractual controls. ''Embedded'' means security requirements, assurance rights and supplier incident-notification clauses are embedded in contracts for critical suppliers.',
  1.5, 2),
 ('CSRB-B2', 'CSRB-B2-Q3',
  'Do you monitor supplier security posture on an ongoing basis rather than only at onboarding?',
  'Third-party risk changes over time. ''Embedded'' means supplier assurance is continuous (e.g. periodic reviews, attestations, monitoring), not a one-off onboarding check.',
  1.0, 3),
 ('CSRB-C1', 'CSRB-C1-Q1',
  'Do you have the capability to become aware of a significant incident promptly and a defined process to notify the regulator (and NCSC) within 24 hours of awareness?',
  'The Bill mandates rapid initial notification — within 24 hours of becoming aware of a significant incident. ''Embedded'' means detection plus a documented, rehearsed 24-hour notification process with named responsible roles and contact routes.',
  2.0, 1),
 ('CSRB-C1', 'CSRB-C1-Q2',
  'Can you produce and submit the required detailed incident report within 72 hours, covering affected systems, impact and remediation?',
  'A detailed report must follow within 72 hours of awareness. ''Embedded'' means you have a template, evidence-collection process and the capability to compile and submit a compliant 72-hour report.',
  2.0, 2),
 ('CSRB-C1', 'CSRB-C1-Q3',
  'Are reporting thresholds (what constitutes a ''significant'' incident) defined, documented and understood by relevant staff?',
  'Timely reporting depends on staff recognising a reportable incident. ''Embedded'' means thresholds are defined, documented and communicated, and staff know how and when to trigger the reporting process.',
  1.5, 3),
 ('CSRB-C2', 'CSRB-C2-Q1',
  'Do you maintain a documented incident response plan with defined roles, escalation and regulator-communication steps?',
  'Reporting must be backed by response. ''Embedded'' means a documented IR plan exists that defines roles, escalation, decision-making and regulator/NCSC communication, integrated with the reporting duties.',
  1.5, 1),
 ('CSRB-C2', 'CSRB-C2-Q2',
  'Are incident response exercises (e.g. tabletop) run at least annually, with lessons fed back into your controls and plans?',
  'Plans must be tested. ''Embedded'' means IR exercises including the reporting workflow are run at least annually and improvements are tracked to completion.',
  1.5, 2),
 ('CSRB-D1', 'CSRB-D1-Q1',
  'Do you maintain and test business continuity and disaster recovery plans so essential and digital services can be restored within agreed timeframes after disruption?',
  'The Bill is centred on resilience. ''Embedded'' means BC/DR plans exist with defined recovery objectives (RTO/RPO) for essential/digital services and are tested at least annually.',
  2.0, 1),
 ('CSRB-D1', 'CSRB-D1-Q2',
  'Are backups protected (e.g. offline or immutable) and is their restoration tested regularly?',
  'Recoverability underpins resilience. ''Embedded'' means backups are protected against tampering/ransomware (offline/immutable) and restoration is tested on a defined schedule.',
  1.5, 2),
 ('CSRB-D2', 'CSRB-D2-Q1',
  'Do you have a process to cooperate with regulators — responding to information requests and inspections and complying with enforcement directions?',
  'The Bill strengthens regulator investigatory and enforcement powers. ''Embedded'' means you can respond to information notices, support inspections and act on enforcement directions, with ownership assigned.',
  1.5, 1),
 ('CSRB-D2', 'CSRB-D2-Q2',
  'Do you participate in threat information sharing (e.g. NCSC) and act on advisories relevant to your sector?',
  'Resilience is improved by shared intelligence. ''Embedded'' means you consume and act on NCSC/sector advisories and, where appropriate, share information back; lower levels reflect ad hoc or no engagement.',
  1.0, 2)
ON CONFLICT (code) DO NOTHING;

-- ---------- CSRB answer options (5-point maturity scale) ----------
-- Embedded = 1.0, Managed = 0.75, Defined = 0.5, Initial = 0.25, Not in place = 0.0
INSERT INTO answer_options (question_id, label, score, sort_order)
SELECT q.id, v.label, v.score, v.so
FROM questions q
JOIN caf_principles p ON p.id = q.principle_id AND p.framework_id = 'csrb'
CROSS JOIN (VALUES
    ('Embedded & optimised', 1.000, 1),
    ('Managed',              0.750, 2),
    ('Defined',              0.500, 3),
    ('Initial / ad hoc',     0.250, 4),
    ('Not in place',         0.000, 5)
) AS v(label, score, so);

-- ---------- CSRB remediation suggestions (principle-specific) ----------
INSERT INTO remediation_suggestions (id, title, detail, priority, effort) VALUES
 ('c5b10000-0000-0000-0000-000000000001',
  'Determine and register your regulatory scope (CSRB-A1)',
  'Assess your organisation against the Bill''s in-scope categories (OES, RDSP, managed service provider, data centre, large load controller, critical supplier), document the determination, and register/notify the appropriate regulator. Keep the status under periodic review as the Bill''s secondary legislation develops. Addresses CSRB Principle A1 (Regulatory Scope & Registration).', 1, 'low'),
 ('c5b20000-0000-0000-0000-000000000002',
  'Establish board accountability and resilience governance (CSRB-A2)',
  'Appoint a named board-level owner accountable for the Bill''s obligations, document roles and reporting lines, and approve a policy set covering cyber resilience duties with a planned review cycle and board reporting. Addresses CSRB Principle A2 (Governance, Roles & Accountability).', 1, 'medium'),
 ('c5b30000-0000-0000-0000-000000000003',
  'Implement proportionate risk management and baseline controls (CSRB-B1)',
  'Adopt a documented risk assessment method for your network and information systems, record risks with owners, and implement appropriate and proportionate measures — MFA, least-privilege access, timely patching, secure configuration and monitoring — evidenced against the risk. Addresses CSRB Principle B1 (Risk Assessment & Proportionate Measures).', 1, 'high'),
 ('c5b40000-0000-0000-0000-000000000004',
  'Manage supply-chain and MSP cyber risk (CSRB-B2)',
  'Identify and tier critical suppliers and managed service providers, embed security requirements, assurance rights and incident-notification clauses in contracts, and monitor supplier posture on an ongoing basis. Addresses CSRB Principle B2 (Supply Chain & Third-Party Risk).', 2, 'high'),
 ('c5b50000-0000-0000-0000-000000000005',
  'Build a 24h/72h mandatory incident reporting capability (CSRB-C1)',
  'Define reportable-incident thresholds, ensure detection can surface significant incidents promptly, and stand up a rehearsed process to notify the regulator/NCSC within 24 hours and submit a detailed report within 72 hours, with named roles and templates. Addresses CSRB Principle C1 (Detection & Mandatory Reporting).', 1, 'medium'),
 ('c5b60000-0000-0000-0000-000000000006',
  'Document and exercise incident response (CSRB-C2)',
  'Maintain an incident response plan defining roles, escalation, decision-making and regulator communication, integrated with the reporting duties, and exercise it at least annually with lessons tracked to closure. Addresses CSRB Principle C2 (Incident Response & Recovery).', 2, 'medium'),
 ('c5b70000-0000-0000-0000-000000000007',
  'Strengthen operational resilience and recovery (CSRB-D1)',
  'Maintain and test business continuity and disaster recovery plans with defined recovery objectives for essential/digital services, and protect backups (offline/immutable) with regularly tested restoration. Addresses CSRB Principle D1 (Operational Resilience & Continuity).', 1, 'high'),
 ('c5b80000-0000-0000-0000-000000000008',
  'Operationalise regulatory cooperation and information sharing (CSRB-D2)',
  'Establish a process to respond to regulator information requests, inspections and enforcement directions, assign ownership, and engage with NCSC/sector threat information sharing so advisories are actioned. Addresses CSRB Principle D2 (Regulatory Cooperation & Information Sharing).', 3, 'low')
ON CONFLICT (id) DO NOTHING;

-- ---------- Map each CSRB principle to its remediation ----------
INSERT INTO remediation_mappings (principle_id, remediation_id, threshold) VALUES
 ('CSRB-A1', 'c5b10000-0000-0000-0000-000000000001', 0.700),
 ('CSRB-A2', 'c5b20000-0000-0000-0000-000000000002', 0.700),
 ('CSRB-B1', 'c5b30000-0000-0000-0000-000000000003', 0.700),
 ('CSRB-B2', 'c5b40000-0000-0000-0000-000000000004', 0.700),
 ('CSRB-C1', 'c5b50000-0000-0000-0000-000000000005', 0.700),
 ('CSRB-C2', 'c5b60000-0000-0000-0000-000000000006', 0.700),
 ('CSRB-D1', 'c5b70000-0000-0000-0000-000000000007', 0.700),
 ('CSRB-D2', 'c5b80000-0000-0000-0000-000000000008', 0.700)
ON CONFLICT (principle_id, remediation_id) DO NOTHING;